﻿using System;
using System.Data;
using System.Xml;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics;
using WoSai.Base.Config;

namespace WoSai.Base.Data.SqlServer
{
    public class SqlDBHelper
    {
        private static string connectionString = null;
        private static SqlClientFactory sqlFactory = null;
        /// <summary>
        /// 查询次数统计
        /// </summary>
        private static int queryCount = 0;
        private static Dictionary<int, SqlParameter[]> paramDictonary = new Dictionary<int, SqlParameter[]>();
        private static object locker = new object();
        #region DEBUG模式
#if DEBUG
        /// <summary>
        /// 查询细节（参数名，参数类型，参数值，查询语句/存储过程名，运行时间，）
        /// </summary>
        private static string queryDetail = "";
        public static string QueryDetail
        {
            get { return SqlDBHelper.queryDetail; }
            set { SqlDBHelper.queryDetail = value; }
        }
        private static string GetQueryDetail(string commandText, DateTime dtStart, DateTime dtEnd, SqlParameter[] cmdParams)
        {
            string tr = "<tr style=\"background: rgb(255, 255, 255) none repeat scroll 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;\">";
            string colums = "";
            string dbtypes = "";
            string values = "";
            string paramdetails = "";
            if (cmdParams != null && cmdParams.Length > 0)
            {
                foreach (SqlParameter param in cmdParams)
                {
                    if (param == null)
                    {
                        continue;
                    }
                    colums += "<td>" + param.ParameterName + "</td>";
                    dbtypes += "<td>" + param.DbType.ToString() + "</td>";
                    values += "<td>" + param.Value.ToString() + "</td>";
                }
                paramdetails = string.Format("<table width=\"100%\" cellspacing=\"1\" cellpadding=\"0\" style=\"background: rgb(255, 255, 255) none repeat scroll 0%; margin-top: 5px; font-size: 12px; display: block; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;\">{0}{1}</tr>{0}{2}</tr>{0}{3}</tr></table>", tr, colums, dbtypes, values);
            }
            return string.Format("<center><div style=\"border: 1px solid black; background:#FFF; margin: 2px; padding: 1em; text-align: left; width: 96%; clear: both;\"><div style=\"font-size: 12px; float: right; width: 100px; margin-bottom: 5px;\"><b>TIME:</b> {0}</div><span style=\"font-size: 12px;\">{1}{2}</span></div><br /></center>", dtEnd.Subtract(dtStart).TotalMilliseconds / 1000, commandText, paramdetails);
        }
#endif
        #endregion
        #region 属性
        public static string ConnectionString
        {
            get
            {
                if (SqlDBHelper.connectionString == null)
                {
                    SqlDBHelper.connectionString = DefaultConfig.DBconnectionstring;
                }

                return SqlDBHelper.connectionString; 
            }
            set { SqlDBHelper.connectionString = value; }
        }

        /// <summary>
        /// 查询次数
        /// </summary>
        public static int QueryCount
        {
            get { return SqlDBHelper.queryCount; }
            set { SqlDBHelper.queryCount = value; }
        }
        public static SqlClientFactory SqlFactory
        {
            get
            {
                if (SqlDBHelper.sqlFactory == null)
                {
                    SqlDBHelper.sqlFactory = SqlClientFactory.Instance;
                }
                return SqlDBHelper.sqlFactory;
            }
            set { SqlDBHelper.sqlFactory = value; }
        }
        #endregion

        #region private method

        /// <summary>
        /// 为sqlcommand,添加数据库连接/事务/命令类型/参数的属性；
        /// </summary>
        /// <param name="command">command实例</param>
        /// <param name="connection">数据库连接</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">command类型</param>
        /// <param name="commandText">command内容</param>
        /// <param name="commandParameters">command参数</param>
        /// <param name="needCloseConnection">返回类型，是否需要关闭链接（如果在此方法内打开了数据库连接则此参数返回true）</param>
        private static void PackCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction,
            CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool needCloseConnection)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (string.IsNullOrEmpty(commandText)) throw new ArgumentNullException("commandText");
            if (connection.State != ConnectionState.Open)
            {
                needCloseConnection = true;
                connection.Open();
            }
            else
            {
                needCloseConnection = false;
            }

            command.Connection = connection;
            command.CommandText = commandText;

            if(transaction !=null)
            {
                if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                command.Transaction = transaction;
            }
            command.CommandType = commandType;
            if (commandParameters != null)
            {
                PackParameters(command, commandParameters);
            }
            return;
        }

        /// <summary>
        /// 为command添加参数
        /// 检查未分配值的输出参数,将其分配以DBNull.Value.
        /// </summary>
        /// <param name="command">command实例</param>
        /// <param name="commandParameters">参数数组</param>
        private static void PackParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandParameters != null)
            {
                foreach (SqlParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        if ((p.Direction == ParameterDirection.Input || p.Direction == ParameterDirection.InputOutput)
                            && (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
 
        }

        private static SqlParameter[] CreatSpParameters(SqlConnection connection, string spName, bool hasOutput)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (string.IsNullOrEmpty(spName)) throw new ArgumentNullException("spName");
            SqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = spName;
            cmd.CommandType = CommandType.StoredProcedure;
            connection.Open();
            SqlCommandBuilder.DeriveParameters(cmd);
            connection.Close();
            if (!hasOutput)
            {
                cmd.Parameters.RemoveAt(0);
            }
            SqlParameter[] spParameters = new SqlParameter[cmd.Parameters.Count];
            cmd.Parameters.CopyTo(spParameters, 0);
            foreach (SqlParameter p in spParameters)
            {
                p.Value = DBNull.Value;
            }
            return spParameters;
        }

        private static SqlParameter MakeParam(string paramName, SqlDbType dbType, Int32 Size)
        {
            SqlParameter param;

            if (Size > 0)
                param = new SqlParameter(paramName, dbType, Size);
            else
                param = new SqlParameter(paramName, dbType);

            return param;
        }
        #endregion

        #region ExecuteNonQuery return int

        /// <summary>
        /// 执行指定数据库连接对象的命令
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new DbParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>
        /// <param name="commandText">T存储过程名称或SQL语句</param>
        /// <param name="commandParameters">SqlParamter参数数组</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            SqlCommand cmd= SqlFactory.CreateCommand() as SqlCommand;
            bool needCloseconnection = false;
            PackCommand(cmd,connection,null,commandType,commandText,commandParameters,out needCloseconnection);
#if DEBUG
            DateTime startTime=DateTime.Now;
#endif
            int effect = cmd.ExecuteNonQuery();
#if DEBUG
            DateTime endTime=DateTime.Now;
            queryDetail+=GetQueryDetail(commandText,startTime,endTime,commandParameters);
            queryCount++;
#endif
            cmd.Parameters.Clear();
            if (needCloseconnection) {connection.Close();}
            return effect;


        }
        #endregion

        #region ExecuteReader return SqlDataReader

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="transaction"></param>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <param name="isInternalConnection">bool数据库连接对象是由调用者提供还是由SqlDbHelper提供</param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction,
            CommandType commandType, string commandText, SqlParameter[] commandParameters, bool isInternalConnection)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            SqlCommand cmd = sqlFactory.CreateCommand() as SqlCommand;
            bool needCloseConnection = false;
            try
            {
                PackCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out needCloseConnection);
                SqlDataReader dataReader;
#if DEBUG
                DateTime startTime = DateTime.Now;
#endif
                if (isInternalConnection)
                {
                    dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    dataReader = cmd.ExecuteReader();
                }
#if DEBUG
                DateTime endTime = DateTime.Now;
                queryDetail += GetQueryDetail(commandText, startTime, endTime, commandParameters);
                queryCount++;
#endif
                bool clearParam = true;
                foreach (SqlParameter p in cmd.Parameters)
                {
                    if (p.Direction != ParameterDirection.Input)
                    {
                        clearParam = false;
                    }
                }
                if (clearParam)
                {
                    cmd.Parameters.Clear();
                }
                return dataReader;
            }
            catch
            {
                if (needCloseConnection) connection.Close();
                throw;
            }

        }
        public static SqlDataReader ExecuteReader(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (string.IsNullOrEmpty(ConnectionString)) throw new ArgumentNullException("ConnetionString");
            SqlConnection connection = null;
            try
            {
                connection = (SqlConnection)SqlFactory.CreateConnection();
                connection.ConnectionString = ConnectionString;
                connection.Open();
                return ExecuteReader(connection, null, commandType, commandText, commandParameters, true);
            }
            catch
            {
                if (connection != null) connection.Close();
                throw;
            }

        }
        #endregion

        #region ExecuteScalar return object

        public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            SqlCommand cmd = sqlFactory.CreateCommand() as SqlCommand;
            bool needCloseconnection = false;
            PackCommand(cmd, connection, null, commandType, commandText, commandParameters, out needCloseconnection);
            object result = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            if (needCloseconnection) connection.Close();

            return result;
        }
        #endregion

        #region ExecuteDataset return DataSet

        public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            SqlCommand cmd = SqlFactory.CreateCommand() as SqlCommand;
            bool needCloseconnection = false;
            PackCommand(cmd, connection, null, commandType, commandText, commandParameters, out needCloseconnection);
            using(SqlDataAdapter da=sqlFactory.CreateDataAdapter() as SqlDataAdapter)
            {
                da.SelectCommand = cmd;
                DataSet ds = new DataSet();
#if DEBUG
                DateTime startTime = DateTime.Now;
#endif
                da.Fill(ds);
#if DEBUG
                DateTime endTime = DateTime.Now;
                queryDetail += GetQueryDetail(commandText, startTime, endTime, commandParameters);
                queryCount++;
#endif
                cmd.Parameters.Clear();
                if (needCloseconnection) connection.Close();
                return ds;
            }

        }
        #endregion

        #region 检索缓存参数
        private static SqlParameter[] GetCachedParametersBySpName(SqlConnection connection, string spName, bool hasOutput)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (string.IsNullOrEmpty(spName)) throw new ArgumentNullException("spName");
            int dictKey = (connection.ConnectionString + ":" + spName + (hasOutput ? ":include ReturnValue Parameter" : "")).GetHashCode();
            SqlParameter[] cachedParameters;
            if (!paramDictonary.ContainsKey(dictKey))
            {
                cachedParameters = CreatSpParameters(connection,spName,hasOutput);
                paramDictonary.Add(dictKey, cachedParameters);
            }
            else
            {
                cachedParameters = paramDictonary[dictKey];
            }
            return cachedParameters;
        }
        #endregion

        #region 生成参数
        public static SqlParameter MakeInParam(string ParamName, SqlDbType dbType, int Size, object Value)
        {
            return MakeParam(ParamName, dbType, Size, ParameterDirection.Input, Value);
        }
        public static DbParameter MakeOutParam(string ParamName, SqlDbType dbType, int size)
        {
            return MakeParam(ParamName, dbType, size, ParameterDirection.Output, null);
        }

        public static SqlParameter MakeParam(string ParamName, SqlDbType dbType, Int32 Size, ParameterDirection direction, object Value)
        {
            SqlParameter param;

            param = MakeParam(ParamName, dbType, Size);

            param.Direction = direction;
            if (!(direction == ParameterDirection.Output && Value == null))
                param.Value = Value;

            return param;
        }
        #endregion

    }
}